package inglar.autotc.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import inglar.autotc.shared.Values;
import inglar.autotc.shared.db.Actions;
import inglar.autotc.shared.db.Clients;
import inglar.autotc.shared.db.Templates;

public class Database {
	
	private String dbname = "autotc";
	
	private String driver = "org.gjt.mm.mysql.Driver";
	
	private String dbuser = Values.DAEMON_DEFAULT_DBUSER;
	
	private String dbpass = Values.DAEMON_DEFAULT_DBPASS;
	
	private String dbhost = Values.DAEMON_DEFAULT_DBHOST;
	
	private String dbport = Values.DAEMON_DEFAULT_DBPORT;
	
	private Connection conn = null;
	
	private Connection getConnection() throws InstantiationException, 
			IllegalAccessException, ClassNotFoundException, SQLException {
		if(conn != null && !conn.isClosed()) return conn;
		Connection retval = null;
		Class.forName(driver).newInstance();
		retval = DriverManager.getConnection("jdbc:mysql://" + this.dbhost + 
				":" + this.dbport + "/" + this.dbname, this.dbuser, 
				this.dbpass);
		this.conn = retval;
		return retval;
	}
	
	/**
	 * Logs a user based on a given username-password pair and stores the jsessionid (from a servlet) and returns the user'd userid.
	 * @param username
	 * @param password
	 * @param jsessionid
	 * @return the user's userid, <code>null</code> if login fails
	 */
	public String userLogin(String username, String password, String jsessionid) {
		String retval = null;
		boolean loginSuccess = false;
		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			
			Connection con = DriverManager.getConnection(
					"jdbc:mysql://localhost/autotc", 
					"root", 
					"password");
			
			con.setAutoCommit(false);
			
			Statement stmt = con.createStatement();
			
			String sql1 = "SELECT `userid` FROM `users` WHERE `username`='" + username +"' AND `password`='" + password + "'";
			ResultSet rs1 = stmt.executeQuery(sql1);
			
			if(rs1.first()) { // if there is a username-password pair
				retval = rs1.getString(1);
				
				String sql2 = "UPDATE `userhook` SET value = '" + retval + "' WHERE `logtype` = 'current_user'";
				stmt.executeUpdate(sql2);
				
				String sql3 = "UPDATE `userhook` SET value = '" + jsessionid + "' WHERE `logtype` = 'session_id'";
				stmt.executeUpdate(sql3);
				
				con.commit();
			}
			
			rs1.close(); 
			stmt.close();
			con.close();
			
			loginSuccess = true;
		} catch(Throwable t) {
			Host.out(Values.ErrorCode.D005badlogin + "\nDetails: caught " + t.getClass() + "\nmsg: \"" + t.getMessage() + "\"");
		} 
		
		if(loginSuccess) return retval;
		else return null;
	}

	private Database() {
		
	}
	
	/**
	 * Creates a database connection to this application's database server.
	 * <!--  
	 * XXX The database used is MySQL, and so a MySQL JDBC is used.
	 * --> 
	 * 
	 * @param dbhost 
	 * the domain name or IP address of the database server; 
	 * 
	 * @param dbport
	 * the port number used to connect; an invalid port defaults this value to
	 * {@link Values#DAEMON_DEFAULT_DBPORT}
	 * 
	 * @param dbuser
	 * the user name used for the database
	 * 
	 * @param dbpass
	 * the password for <b>dbuser</b>
	 * 
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SQLException if the parameters used did not allow connection to the database
	 */
	public Database(String dbhost, String dbport, String dbuser, String dbpass) 
			throws InstantiationException, IllegalAccessException, 
			ClassNotFoundException, SQLException {
		this();
		
		if(dbhost != null && !dbhost.trim().isEmpty()) this.dbhost = dbhost.trim();
		if(dbuser != null && !dbuser.trim().isEmpty()) this.dbuser = dbuser.trim();
		if(dbpass != null && !dbpass.trim().isEmpty()) this.dbpass = dbpass.trim();
		if(Integer.getInteger(dbport) != null) this.dbport.trim();
		
		this.conn = getConnection();
	}
	
//	public class Actions {
//		static final String tname = "actions";
//		
//	}
//	
//	public class Clients {
//		
//	}
//	
//	public class Devices {
//		
//	}
//	
//	public class Templates {
//		
//	}
	
	public static void main(String[] args) {
//		try {
//			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
//			
//			Connection con = DriverManager.getConnection(
//					"jdbc:mysql://localhost/autotc", 
//					"root", 
//					"password");
//			
//			con.setAutoCommit(false);
//			
//			Statement stmt = con.createStatement();
//			
//			String sql1 = "SELECT `id` FROM `autotc` WHERE `prio` = '" + prio + "' AND `detect` = '" + detect + "' AND `action` = '" + action + "'";
//			
//			ResultSet rs1 = stmt.executeQuery(sql1);
//			if(rs1.first()) {
//				// line already exists, do nothing
//			} else {
//				String sql2 = "INSERT INTO `autotc` (`prio`, `detect`, `action`) VALUES ('" + prio + "', '" + detect + "', '" + action + "')";
//				if(stmt.executeUpdate(sql2) < 1) retval = "No rows updated.";
//			}
//			
//			stmt.close();
//			con.close();
//		} catch(Throwable t) {
//			Host.out(Values.ErrorCode.D009badsetscript + "\nDetails: caught " + t.getClass() + "\nmsg: \"" + t.getMessage() + "\"");
//			t.printStackTrace();
//		}
		System.out.println(Database.class.getName());
		Database d = null;
		try {
			d = new Database("localhost", "3306", "root", "password");
			System.out.println(d);
		} catch (Throwable caught) {
//			Host.throwableDump(caught);
		}
	}

	public boolean userHasIssuedCommand() throws Exception {
		boolean retval = true;
		
		try {
			Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			String sql1 = "SELECT `value` FROM `userhook` WHERE `logtype` = 'action'";
			ResultSet rs1 = stmt.executeQuery(sql1);
			if(rs1.first()) {
				if(rs1.getString(1) == null) { 
					retval = false;
				}
				else {
					retval = true;
				}
			} else {
				retval = false;
			}
		} catch(Exception e) {
//			Host.err(Host.timeStamp() + " Error collecting data from DAO");
//			Host.err(Host.throwableDump(e));
			throw e;
//			retval = false;
		}
		
		return retval;
	}

	public Actions[] getActions() throws Exception {
		Actions[] retval = null;
		Vector<Actions> v = new Vector<Actions>();
		try {
			Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			String sql1 = "SELECT `id`, `prio`, `detect`, `action` FROM `actions`";
			ResultSet rs1 = stmt.executeQuery(sql1);
			while(rs1.next()) {
				try {
					Actions a = new Actions();
					a.setId(rs1.getString(1));
					a.setPrio(rs1.getString(2));
					a.setDetect(rs1.getString(3));
					a.setAction(rs1.getString(4));
					v.add(a);
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
			}
		} catch(Exception e) {
//			Host.err(Host.timeStamp() + " Error collecting data from DAO");
//			Host.err(Host.throwableDump(e));
			throw e;
		}
		
		retval = new Actions[v.size()];
		v.copyInto(retval);
		return retval;
	}

	public Clients[] getClients() throws Exception {
		Clients[] retval = null;
		Vector<Clients> v = new Vector<Clients>();
		try {
			Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			String sql1 = "SELECT `id`, `ip`, `port` FROM `clients`";
			ResultSet rs1 = stmt.executeQuery(sql1);
			while(rs1.next()) {
				try {
					Clients c = new Clients();
					c.setId(rs1.getString(1));
					c.setAddress(rs1.getString(2));
					c.setPort(rs1.getString(3));
					v.add(c);
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
			}
		} catch(Exception e) {
			throw e;
		}
		
		retval = new Clients[v.size()];
		v.copyInto(retval);
		return retval;
	}

	public Templates getTemplate(String device, String templatename) throws Exception {
		Templates retval = null;
		if(device == null) throw new IllegalArgumentException("param templatename must not be null");
		
		String exttemp = "";
		if((device != null) && (!device.isEmpty())) {  
			exttemp = " AND `device`='" + device + "'";
		} else { 
			// bear with whatever first row MySQL returns
		}
		
		try {
			Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			String sql1 = "SELECT `id`, `name`, `device`, `script` FROM `templates` WHERE `name`='" + templatename + "' " + exttemp;
			ResultSet rs1 = stmt.executeQuery(sql1);
			if(rs1.first()) {
				try {
					retval = new Templates();
					retval.setId(rs1.getString(1));
					retval.setName(rs1.getString(2));
					retval.setDevice(rs1.getString(3));
					retval.setScript(rs1.getString(4));
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
			}
		} catch(Exception e) {
			throw e;
		}
		
		return retval;
	}

}
